I have checked your code and implemented same with one table from SQL and checked. It’s working fine if records exist in the table.
Check the below same code in SQL which working fine.
here i just created one table to check username and Password and implemented same code in SQL just my sql script is diffrent.
SQL
CREATE TABLE [dbo].[Users](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[Username] [nvarchar](20) NOT NULL,
[Password] [nvarchar](20) NOT NULL,
[Email] [nvarchar](30) NOT NULL
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [Users] ([UserId],[Username] ,[Password],[Email])
VALUES(1,'Mudassar','12345','mudassar@aspsnippets.com')
GO
INSERT INTO [Users] ([UserId],[Username] ,[Password],[Email])
VALUES(2,'Ram','12345','ram@aspsnippets.com')
GO
Form
sqlcontrol1.vb
Imports System.Data.SqlClient
Public Class sqlcontrol1
Private DBCon As New SqlConnection
Private DBCmd As SqlCommand
' DB DATA
Public DBDA As SqlDataAdapter
Public DBDT As DataTable
Friend Parameters As SqlParameter
' QUERY PARAMETERS
Private Params As New List(Of SqlParameter)
' QUERY STATISTICS
Public RecordCount As Integer
Public Exception As String
Public Sub New()
End Sub
Public Sub New(ConnectionString As String)
DBCon.ConnectionString = "Server=192.168.0.1\SQL2005;Database=LoginDB;uid=sa;pwd=pass@123;"
End Sub
' EXECUTE QUERY SUB
Public Sub ExecQuery(Query As String)
RecordCount = 0
Exception = ""
DBCon.ConnectionString = "Server=192.168.0.1\SQL2005;Database=LoginDB;uid=sa;pwd=pass@123;"
MsgBox("connection succesful ")
Try
DBCon.Open()
If DBCon.State = ConnectionState.Open Then MsgBox("connection open ")
' CREATE DB COMMAND
DBCmd = New SqlCommand(Query, DBCon)
' LOAD PARAMS INTO DB COMMAND
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
' CLEAR PARAM LIST
Params.Clear()
' EXECUTE COMMAND & FILL DATASET
DBDA = New SqlDataAdapter(DBCmd)
DBDT = New DataTable
RecordCount = DBDA.Fill(DBDT)
' sqlAdp = New SqlDataAdapter("select * from users", cnn)
Catch ex As Exception
Exception = "ExecQuery Error: " & vbNewLine & ex.Message
Finally
' CLOSE CONNECTION
If DBCon.State = ConnectionState.Open Then DBCon.Close()
End Try
End Sub
' ADD PARAMS
Public Sub AddParam(ByVal Name As String, Value As Object)
Dim NewParam As New SqlParameter(Name, Value)
Params.Add(NewParam)
End Sub
' ERROR CHECKING
Public Function HasException(Optional Report As Boolean = False) As Boolean
If String.IsNullOrEmpty(Exception) Then Return False
If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
Return True
End Function
End Class
MySqlParameter.vb
Imports System.Data.SqlClient
Imports System.Object
Imports System.MarshalByRefObject
Imports System.Data.Common.DbParameter
Class MySqlParameter
Public Params As New List(Of MySqlParameter)
Private _name As String
Private _NUMBERS As String
Private _value As Object
Sub New(ByVal Name As String, Value As Object)
' TODO: Complete member initialization
_name = Name
_value = Value
End Sub
End Class
Form1.vb
Imports System.Data.SqlClient
Public Class Form1
Public MySql As New sqlcontrol1
Public Function NOTEMPTY(ByVal TEXT As String) As Boolean
Return Not String.IsNullOrEmpty(TEXT)
End Function
Public Function EMPTY(ByVal TEXT As String) As Boolean
Return String.IsNullOrEmpty(TEXT)
Return False
End Function
Private Function noerrors(Optional ByVal report As Boolean = False) As Boolean
If Not String.IsNullOrEmpty(MySql.Exception) Then
If report = True Then MsgBox(MySql.Exception)
Return False
Else
Return True
End If
End Function
Private Sub Form1_Shown(sender As Object, e As EventArgs)
End Sub
Private Sub txt_username_KeyDown(sender As Object, e As KeyEventArgs) Handles txt_username.KeyDown
If e.KeyCode = Keys.Enter Then
End If
End Sub
Private Sub txt_password_KeyDown(sender As Object, e As KeyEventArgs) Handles txt_password.KeyDown
If e.KeyCode = Keys.Enter Then
End If
End Sub
Dim x_name As String = ""
Private DBCon As New SqlConnection
Private Command As New SqlCommand
Dim x_f_name As String = ""
Dim x_l_name As String = ""
Dim X_COUNT As Integer = 0
Private Sub cmd_go_Click(sender As Object, e As EventArgs) Handles cmd_go.Click
MySql.ExecQuery("SELECT [Username] ,[Password] FROM [Users] ORDER BY [UserId] ASC;")
X_COUNT = MySql.RecordCount
MsgBox(X_COUNT)
MySql.AddParam("@x1", txt_username.Text)
MySql.AddParam("@x2", txt_password.Text)
MySql.ExecQuery("SELECT [UserId],[Username] ,[Password],[Email] FROM [Users] WHERE [Username] = @x1 AND [Password] = @x2 ;")
If noerrors(True) = False Then MsgBox(MySql.Exception)
Dim r As DataRow = MySql.DBDT.Rows(0)
x_f_name = r("UserId").ToString
x_l_name = r("Email").ToString
MsgBox(x_f_name & Space(10) & x_l_name)
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
End Sub
End Class
Screenshot